#!/usr/bin/env python
# -*- encoding: utf-8 -*-
'''
@File    :   curd.py
@Time    :   2021/02/08 00:12:14
@Author  :   superjin
@Version :   1.0
'''

from hashlib import new
from os import name
from typing import Any, Optional, List
from sqlalchemy import func,and_ , or_,text
from sqlalchemy.orm import Session
from core.schemas.participant import ParticipantCreate, ParticipantDelete
from models.user import User
from models.participant import Participant
from models.classroom import Classroom
from models.reservation import Reservation
from datetime import timedelta,datetime



class CRUDParticipant:
    def remove(self,db: Session,delete_participant:ParticipantDelete ):
        db.query(Participant).filter_by(Participant.participant_id.in_(
            delete_participant.participant_ids)).delete(synchronize_session=False)
        db.commit()
        return {}
    
    def create(self, db: Session, reservation_id:int,users: []) :
        participants = []
        for i in users:
            participants.append(Participant(reservation_id,i))
        db.add_all(participants)
        db.commit()
        return participants
    def checkin(self, db: Session,user_id:int,check_in_time = datetime.now()) :
        sql = """
        UPDATE participant p
        JOIN reservation r ON p.reservation_id = r.reservation_id
        SET p.check_in = :check_in_time
        WHERE p.user_id = :user_id
        AND p.check_in IS NULL
        AND r.start_time BETWEEN DATE_SUB(:check_in_time, INTERVAL 10 MINUTE)
                            AND DATE_ADD(:check_in_time, INTERVAL 10 MINUTE);
        """

        # 执行SQL，传递动态参数
        result = db.execute(
            text(sql), 
            {'user_id': user_id, 'check_in_time': check_in_time}
        )
        db.commit()

    def checkout(self, db: Session, user_id:int,check_out_time = datetime.now()) :
        sql = """
        UPDATE participant p
        JOIN reservation r ON p.reservation_id = r.reservation_id
        SET p.check_out = :check_out_time
        WHERE p.user_id = :user_id
        AND p.check_out IS NULL
        AND r.end_time BETWEEN DATE_SUB(:check_out_time, INTERVAL 10 MINUTE)
                            AND DATE_ADD(:check_out_time, INTERVAL 10 MINUTE);
        """

        # 执行SQL，传递动态参数
        result = db.execute(
            text(sql), 
            {'user_id': user_id, 'check_out_time': check_out_time}
        )
        db.commit()

    def list_schedule(self,db:Session,user_id,start_time:str):
        res = db.query(Participant.check_in,
                        Participant.status,
                        Participant.check_out, 
                        Reservation.reservation_id,
                        Reservation.start_time,
                        Reservation.end_time,
                        Reservation.reservation_type,
                        Reservation.description,
                        Reservation.creator_id,
                        User.name,
                        Classroom.room_number,
                        Classroom.building_name,
                        Classroom.location).join(Reservation, 
                Participant.reservation_id == Reservation.reservation_id).join(Classroom,
                Reservation.classroom_id == Classroom.classroom_id).filter(and_(User.user_id == Reservation.creator_id)).filter(
                Participant.user_id == user_id).filter(and_(Reservation.start_time >= start_time)).all()
        new_res = []
        for _ in res:
            new_res.append({
                'check_in' : _.check_in,
                'status' : _.status,
                'check_out' : _.check_out,
                'start_time' : _.start_time.strftime("%Y-%m-%d %H:%M"),
                'end_time' : _.end_time.strftime("%Y-%m-%d %H:%M"),
                'reservation_type' : _.reservation_type,
                'description':_.description,
                'creator_id':_.creator_id,
                'creater_name':_.name,
                'room_number':_.room_number,
                'building_name':_.building_name,
                'reservation_id':_.reservation_id,
                'location':_.location,
            }) 
        return new_res

    def list_participations(self, db: Session, page:int ,page_size:int, user_id) :
        res = db.query(Participant.check_in,
                        Participant.participant_id,
                        Participant.status,
                        Participant.check_out, 
                        Reservation.reservation_id,
                        Reservation.start_time,
                        Reservation.end_time,
                        Reservation.description,
                        func.concat(Classroom.building_name,'-',Classroom.room_number).label('location'),
                        Reservation.reservation_type).join(Reservation, 
                    Participant.reservation_id == Reservation.reservation_id).join(Classroom,
                    Reservation.classroom_id == Classroom.classroom_id).filter(
                    Reservation.reservation_type != 'class').filter(
                    Participant.user_id == user_id).offset((page-1)*page_size).limit(page_size).all()
        total = db.query(func.count(Participant.participant_id)).join(Reservation, 
                    Participant.reservation_id == Reservation.reservation_id).filter(
                    Reservation.reservation_type != 'class').filter(
                    Participant.user_id == user_id).scalar()
        return res,total

    def list_participations_by_time(self, db: Session, page:int ,page_size:int, user_id,start_time):
        res = db.query(Participant.check_in,
                        Participant.participant_id,
                        Participant.status,
                        Participant.check_out, 
                        Reservation.reservation_id,
                        Reservation.start_time,
                        Reservation.end_time,
                        Reservation.description,
                        func.concat(Classroom.building_name,'-',Classroom.room_number).label('location'),
                        Reservation.reservation_type).join(Reservation, 
                    Participant.reservation_id == Reservation.reservation_id).join(Classroom,
                    Reservation.classroom_id == Classroom.classroom_id).filter(
                    Reservation.reservation_type != 'class').filter(
                    Participant.user_id == user_id).filter(
                    and_(Reservation.start_time >= start_time)).offset((page-1)*page_size).limit(page_size).all()
        total = db.query(func.count(Participant.participant_id)).join(Reservation, 
                    Participant.reservation_id == Reservation.reservation_id).filter(
                    Participant.user_id == user_id).filter(
                    Reservation.reservation_type != 'class').filter(
                    and_(Reservation.start_time >= start_time)).scalar()            
        return res,total
    
    def list_classes_attendance(self, db: Session, page:int ,page_size:int, user_id) :
        res = db.query(Participant.check_in,
                        Participant.participant_id,
                        Participant.status,
                        Participant.check_out, 
                        Reservation.reservation_id,
                        Reservation.start_time,
                        Reservation.end_time,
                        Reservation.description,
                        func.concat(Classroom.building_name,'-',Classroom.room_number).label('location'),
                        Reservation.reservation_type).join(Reservation, 
                    Participant.reservation_id == Reservation.reservation_id).join(Classroom,
                    Reservation.classroom_id == Classroom.classroom_id).filter(
                    Reservation.reservation_type == 'class').filter(
                    Participant.user_id == user_id).offset((page-1)*page_size).limit(page_size).all()
        total = db.query(func.count(Participant.participant_id)).join(Reservation, 
                    Participant.reservation_id == Reservation.reservation_id).filter(
                    Reservation.reservation_type == 'class').filter(
                    Participant.user_id == user_id).scalar()
        return res,total

    def list_classes_attendance_by_time(self, db: Session, page:int ,page_size:int, user_id,start_time):
        res = db.query(Participant.check_in,
                        Participant.participant_id,
                        Participant.status,
                        Participant.check_out, 
                        Reservation.reservation_id,
                        Reservation.start_time,
                        Reservation.end_time,
                        Reservation.description,
                        func.concat(Classroom.building_name,'-',Classroom.room_number).label('location'),
                        Reservation.reservation_type).join(Reservation, 
                    Participant.reservation_id == Reservation.reservation_id).join(Classroom,
                    Reservation.classroom_id == Classroom.classroom_id).filter(
                    Reservation.reservation_type == 'class').filter(
                    Participant.user_id == user_id).filter(
                    and_(Reservation.start_time >= start_time)).offset((page-1)*page_size).limit(page_size).all()
        total = db.query(func.count(Participant.participant_id)).join(Reservation, 
                    Participant.reservation_id == Reservation.reservation_id).filter(
                    Reservation.reservation_type == 'class').filter(
                    Participant.user_id == user_id).filter(
                    and_(Reservation.start_time >= start_time)).scalar()            
        return res,total



participant = CRUDParticipant()
